
using Microsoft.EntityFrameworkCore;
using System.Text;
using UwinEducation.Domain.Entities;
using UwinEducation.Domain.IRepositories;
using UwinEducation.Infrastructure.Persistence;
using UwinEducation.Shared.Models;

namespace UwinEducation.Infrastructure.Repositories
{
    /// <summary>
    /// 教师档期仓储实现
    /// </summary>
    public class TeacherAvailabilityRepository : Repository<TeacherAvailability, Guid>, ITeacherAvailabilityRepository
    {
        public TeacherAvailabilityRepository(ApplicationDbContext context) : base(context)
        {
        }

        public async Task<PagedResult<TeacherAvailability>> GetPagedListAsync(TeacherAvailabilityPageRequest pageRequest)
        {
            var query = _context.TeacherAvailabilities
                .Include(ta => ta.Teacher)
                .AsQueryable();

            if (pageRequest.TeacherId.HasValue)
            {
                query = query.Where(ta => ta.TeacherId == pageRequest.TeacherId);
            }
            if (pageRequest.TeacherNo != null)
            {
                query = query.Where(ta => ta.Teacher.TeacherNo == pageRequest.TeacherNo);
            }
            if (pageRequest.Name != null)
            {
                query = query.Where(ta => ta.Teacher.Name == pageRequest.Name);
            }
            if (pageRequest.Phone != null)
            {
                query = query.Where(ta => ta.Teacher.Phone == pageRequest.Phone);
            }
            if (pageRequest.Email != null)
            {
                query = query.Where(ta => ta.Teacher.Email == pageRequest.Email);
            }
            if (pageRequest.Remarks != null)
            {
                query = query.Where(ta => ta.Remarks == pageRequest.Remarks);
            }
            if (pageRequest.StartDate.HasValue && pageRequest.EndDate.HasValue)
            {
                query = query.Where(ta => ta.StartDate >= pageRequest.StartDate.Value.Date && ta.EndDate <= pageRequest.EndDate.Value.Date);
            }
            var total = await query.CountAsync();
            var items = await query
                .OrderBy(ta => ta.Teacher.TeacherNo)
                .ThenBy(ta => ta.DayOfWeek)
                .ThenBy(ta => ta.StartTime)
                .Skip(pageRequest.PageSize * (pageRequest.PageIndex - 1))
                .Take(pageRequest.PageSize)
                .ToListAsync();

            return new PagedResult<TeacherAvailability>
            {
                Total = total,
                Items = items
            };
        }

        /// <summary>
        /// 获取教师档期分页列表（按是否有排课排序）
        /// </summary>
        public async Task<PagedResult<TeacherAvailability>> GetPagedListWithScheduleStatusAsync(TeacherAvailabilityPageRequest pageRequest)
        {
            // 确保使用Include加载Teacher实体，并使用AsNoTracking提高性能
            var query = _context.TeacherAvailabilities
                .Include(ta => ta.Teacher)
                .AsNoTracking()
                .AsSplitQuery()
                .AsQueryable();

            if (pageRequest.TeacherId.HasValue)
            {
                query = query.Where(ta => ta.TeacherId == pageRequest.TeacherId);
            }
            if (pageRequest.TeacherNo != null)
            {
                query = query.Where(ta => ta.Teacher.TeacherNo == pageRequest.TeacherNo);
            }
            if (pageRequest.Name != null)
            {
                query = query.Where(ta => ta.Teacher.Name == pageRequest.Name);
            }
            if (pageRequest.Phone != null)
            {
                query = query.Where(ta => ta.Teacher.Phone == pageRequest.Phone);
            }
            if (pageRequest.Email != null)
            {
                query = query.Where(ta => ta.Teacher.Email == pageRequest.Email);
            }
            if (pageRequest.Remarks != null)
            {
                query = query.Where(ta => ta.Remarks == pageRequest.Remarks);
            }
            if (pageRequest.StartDate.HasValue && pageRequest.EndDate.HasValue)
            {
                query = query.Where(ta => ta.StartDate >= pageRequest.StartDate && ta.EndDate <= pageRequest.EndDate);
            }

            // 计算总记录数
            var total = await query.CountAsync();

            // 先获取分页前的所有教师ID
            var teacherIds = await query.Select(ta => ta.TeacherId).Distinct().ToListAsync();
            
            // 查询每个教师是否有排课
            var teacherSchedules = await _context.TeacherSchedules
                .Where(ts => teacherIds.Contains(ts.TeacherId) && ts.Status == UwinEducation.Shared.Enums.ScheduleStatus.Normal)
                .GroupBy(ts => ts.TeacherId)
                .Select(g => new { TeacherId = g.Key, HasSchedule = true })
                .ToDictionaryAsync(x => x.TeacherId, x => x.HasSchedule);
            
            // 获取所有符合条件的教师档期，但不进行排序和分页
            var allAvailabilities = await query
                .OrderBy(ta => ta.Teacher.TeacherNo) // 基本排序，避免不确定性
                .ThenBy(ta => ta.DayOfWeek)
                .ThenBy(ta => ta.StartDate)
                .ToListAsync();
            
            // 在内存中进行排序和分页
            var availabilities = allAvailabilities
                .OrderBy(ta => teacherSchedules.ContainsKey(ta.TeacherId)) // 没有排课的排在前面
                .ThenBy(ta => ta.Teacher?.TeacherNo ?? string.Empty) // 防止Teacher为null
                .ThenBy(ta => ta.DayOfWeek)
                .ThenBy(ta => ta.StartDate)
                .Skip(pageRequest.PageSize * (pageRequest.PageIndex - 1))
                .Take(pageRequest.PageSize)
                .ToList();

            // 确认每个TeacherAvailability都有Teacher
            foreach (var item in availabilities)
            {
                if (item.Teacher == null)
                {
                    // 如果Teacher为null，手动加载
                    item.Teacher = await _context.Teachers.FirstOrDefaultAsync(t => t.Id == item.TeacherId);
                }
            }

            return new PagedResult<TeacherAvailability>
            {
                Total = total,
                Items = availabilities
            };
        }

        public async Task<TeacherAvailability> GetDetailsByIdAsync(Guid id)
        {
            return await _context.TeacherAvailabilities
                .Include(ta => ta.Teacher)
                .FirstOrDefaultAsync(ta => ta.Id == id);
        }

        public async Task<List<TeacherAvailability>> GetByTeacherIdAsync(Guid teacherId)
        {
            return await _context.TeacherAvailabilities
                .Include(ta => ta.Teacher)
                .Where(ta => ta.TeacherId == teacherId)
                .OrderBy(ta => ta.StartDate)
                .ToListAsync();
        }

        public async Task<List<TeacherAvailability>> GetByDateRangeAsync(Guid teacherId, DateTime startDate, DateTime endDate)
        {
            return await _context.TeacherAvailabilities
                .Where(ta => ta.TeacherId == teacherId &&
                    ta.StartDate >= startDate.Date &&
                    ta.EndDate <= endDate.Date)
                .OrderBy(ta => ta.StartDate)
                .ThenBy(ta => ta.DayOfWeek)
                .ThenBy(ta => ta.StartTime)
                .ToListAsync();
        }

        public async Task<List<TeacherAvailability>> GetByDateRangeAsync(DateTime startDate, DateTime endDate)
        {
            //2025-06-11 10:00:00 - 2025-07-6 11:00:00  2025-07-10 10:00:00会查出来
            
            // 确保使用精确时间比较，不使用.Date属性
            var result = await _context.TeacherAvailabilities
                .Where(ta => ta.StartDate >= startDate && ta.EndDate <= endDate)
                .OrderBy(ta => ta.StartDate)
                .ToListAsync();
                
            // 可以添加记录日志的代码，记录查询条件和结果的日期范围
            // _logger.LogInformation($"查询范围: {startDate} - {endDate}, 结果数量: {result.Count}");
            
            return result;
        }
        public async Task<bool> HasTimeConflictAsync(
            Guid teacherId,
            DateTime startDate,
            DateTime endDate,
            int dayOfWeek,
            TimeSpan startTime,
            TimeSpan endTime,
            Guid? excludeId = null)
        {
            var query = _context.TeacherAvailabilities
                .Where(ta => ta.TeacherId == teacherId &&
                    ta.DayOfWeek == dayOfWeek &&
                    ta.StartDate <= endDate.Date &&
                    ta.EndDate >= startDate.Date &&
                    ((ta.StartTime <= startTime && ta.EndTime > startTime) ||
                     (ta.StartTime < endTime && ta.EndTime >= endTime) ||
                     (ta.StartTime >= startTime && ta.EndTime <= endTime)));

            if (excludeId.HasValue)
            {
                query = query.Where(ta => ta.Id != excludeId.Value);
            }

            return await query.AnyAsync();
        }

        //public async Task AddRangeAsync(List<TeacherAvailability> availabilities)
        //{
        //    await _context.TeacherAvailabilities.AddRangeAsync(availabilities);
        //    await _context.SaveChangesAsync();
        //}

        /// <summary>
        /// 使用SQL批量添加教师档期
        /// </summary>
        /// <param name="availabilities">档期列表</param>
        public async Task BulkInsertAsync(List<TeacherAvailability> availabilities)
        {
            if (availabilities == null || availabilities.Count == 0)
                return;
            
            // 构建批量插入SQL
            StringBuilder sb = new StringBuilder();
            sb.Append("INSERT INTO teacher_availability (Id, TeacherId, StartDate, EndDate, DayOfWeek, StartTime, EndTime, Remarks, CreatedAt, UpdatedAt, CourseType, IsDeleted) VALUES ");
            
            List<string> valuesList = new List<string>();
            List<object> parameters = new List<object>();
            int paramIndex = 0;
            
            foreach (var item in availabilities)
            {
                // 为每个实体生成一个ID
                if (item.Id == Guid.Empty)
                {
                    item.Id = Guid.NewGuid();
                }
                
                // 生成INSERT语句的VALUES部分
                string values = $"(@p{paramIndex}, @p{paramIndex+1}, @p{paramIndex+2}, @p{paramIndex+3}, @p{paramIndex+4}, @p{paramIndex+5}, @p{paramIndex+6}, @p{paramIndex+7}, @p{paramIndex+8}, @p{paramIndex+9}, @p{paramIndex+10}, @p{paramIndex+11})";
                valuesList.Add(values);
                
                // 添加参数
                parameters.Add(item.Id);
                parameters.Add(item.TeacherId);
                parameters.Add(item.StartDate);
                parameters.Add(item.EndDate);
                parameters.Add(item.DayOfWeek);
                parameters.Add(item.StartTime);
                parameters.Add(item.EndTime);
                parameters.Add(item.Remarks ?? string.Empty);
                parameters.Add(item.CreatedAt == default ? DateTime.Now : item.CreatedAt);
                parameters.Add(item.UpdatedAt == default ? DateTime.Now : item.UpdatedAt);
                parameters.Add(item.CourseType);
                parameters.Add(false); // IsDeleted 默认为false
                
                paramIndex += 12;
            }
            
            sb.Append(string.Join(", ", valuesList));
            
            // 执行SQL
            await _context.Database.ExecuteSqlRawAsync(sb.ToString(), parameters.ToArray());
        }

        public async Task DeleteByTeacherIdAsync(Guid teacherId)
        {
            // 使用原生SQL执行批量删除
            string sql = $"DELETE FROM teacher_availability WHERE TeacherId = '{teacherId}'";
            await _context.Database.ExecuteSqlRawAsync(sql, teacherId);
        }
    }
}